import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;


public class DAO {

	public void addMaterial(String name, double orderedAmount, String unit, double expectedPrice, String projectID) throws SQLException{
		//connect
		Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/activiti", "sa", "");
		//generate statement-String
		String stmt = "INSERT INTO TECH_DATA VALUES('"+ name +"',"+ orderedAmount+",'"+ unit +"',"+ expectedPrice +","+ 0.0 + "," + 0.0 + ",'"+ projectID +"');";
		PreparedStatement ps = connection.prepareStatement(stmt);
		ps.execute();
		ps.close();
		connection.close();
	}

	public double getMaterialCosts(String projectID) throws SQLException{

		double sum = 0.0;
		//connect
		Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/activiti", "sa", "");
		PreparedStatement ps = connection.prepareStatement("select * from TECH_DATA where PROJECTID_='" + projectID + "'");
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			sum += rs.getDouble("EXPECTED_PRICE_");
		}
		System.out.println(sum);
		ps.close();
		connection.close();
		return sum;
	}

	public void deleteDBEntries(String projectID) throws SQLException{
		//connect
		Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/activiti", "sa", "");
		PreparedStatement ps = connection.prepareStatement("delete from TECH_DATA where PROJECTID_='" + projectID + "'");
		ps.execute();
		ps.close();
		connection.close();
	}

	public Order getMaterial(String projectID, String partner) throws SQLException{

		Order order = new Order();
		order.setCompany(partner);

		//connect
		Connection connection= DriverManager.getConnection("jdbc:h2:tcp://localhost/activiti", "sa", "");
		PreparedStatement ps = connection.prepareStatement("select * from PARTNERS where COMPANY_='" + order.getCompany() + "';");
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			order.setOffer(rs.getString("OFFER_"));
			order.setMailAddress(rs.getString("E_Mail_"));
			order.setContact(rs.getString("CONTACT_"));
		}
		rs.close();
		ps.close();

		ps = connection.prepareStatement("select * from TECH_DATA where NAME_='" + order.getOffer() + "' and PROJECTID_='" + projectID + "';");
		rs = ps.executeQuery();
		if(rs.next()){
			order.setAmount(rs.getDouble("ORDERED_AMOUNT_"));
			order.setUnit(rs.getString("UNIT_"));
		}
		rs.close();
		ps.close();
		connection.close();

		return order;
	}

	public void saveDeliveries(String projectID, String materialName, double deliveredAmount, double deliveredPrice) throws SQLException{
		//connect
		Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/activiti", "sa", "");

		//update existing data
		PreparedStatement ps = connection.prepareStatement("update TECH_DATA set DELIVERED_AMOUNT_ = " + deliveredAmount + ", REAL_PRICE_ = " + deliveredPrice + "  where PROJECTID_='" + projectID + "' AND NAME_='" + materialName + "'");
		ps.execute();
		ps.close();
		connection.close();
	}

	public HashMap<String, Double> calculateDifferences(String projectID) throws SQLException{

		HashMap<String, Double> missingMaterial = new HashMap<String, Double>();
		double ordered = 0.0;
		double delivered = 0.0;
		double difference = 0.0;

		//connect
		Connection connection= DriverManager.getConnection("jdbc:h2:tcp://localhost/activiti", "sa", "");

		PreparedStatement ps = connection.prepareStatement("select * from TECH_DATA t, PARTNERS p where t.PROJECTID_='" + projectID + "' AND t.NAME_=p.OFFER_");
		ResultSet rs = ps.executeQuery();
		while(rs.next())
		{
			ordered = rs.getDouble("ORDERED_AMOUNT_");
			delivered = rs.getDouble("DELIVERED_AMOUNT_");
			difference = ordered - delivered;

			if(difference > 0.0)
			{
				ComplaintsContainer.getInstance().addComplaint(Math.abs(difference) + " " + rs.getString("UNIT_") + " of " + rs.getString("NAME_"), rs.getString("CONTACT_"));
				missingMaterial.put(rs.getString("NAME_"), difference);
			}
		}
		ps.close();
		connection.close();

		return missingMaterial;
	}

	public double calculateCosts(String projectID) throws SQLException{
		double sumCosts = 0;
		//connect
		Connection connection= DriverManager.getConnection("jdbc:h2:tcp://localhost/activiti", "sa", "");

		PreparedStatement ps = connection.prepareStatement("select * from TECH_DATA where PROJECTID_='" + projectID + "'");
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			sumCosts += rs.getDouble("REAL_PRICE_");
		}
		ps.close();
		connection.close();

		System.out.println("Number of projects: " + ProjectContainer.getInstance().getNoOfProjects());

		return sumCosts;
	}
}
